package com.are.odisea;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class GestionStock {
	private db conexion = null;
	private String codigo; // cedula personal
	private Stock stock;
	
	public static final int ORDER_DESCRIPCION_MATERIAL = 1;
	public static final int ORDER_DESCRIPCION_SUMINISTRO = 2;
	public static final int ORDER_DESCRIPCION_CENTRO = 3;
	public static final int ORDER_DESCRIPCION_BODEGA = 4;
	public static final int ORDER_DESCRIPCION_DEPARTAMENTO = 5;
	
	public static final int FILTER_BY_CODIGO = 1;
	public static final int FILTER_BY_DESCRIPCION = 2;
	
	public static final int FILTER_BY_TERMINE = 3;
	public static final int FILTER_BY_COMIENCE = 4;
	public static final int FILTER_BY_CONTENGA = 5;
	
	
	public db getConexion() {
		return conexion;
	}
	public void setConexion(db conexion) {
		this.conexion = conexion;
	}
	public String getCodigo() {
		return codigo;
	}
	public void setCodigo(String codigo) {
		this.codigo = codigo;
	}
	public GestionStock(db conexion, String codigo) {
		super();
		this.conexion = conexion;
		this.codigo = codigo;
	}
	
	public Stock getStock() {
		return stock;
	}
	public void setStock(Stock stock) {
		this.stock = stock;
	}
	public ArrayList<Stock> DetalleStock(String material,String bodega,String centro,String estado,String suministro) throws SQLException {
		ArrayList<Stock> inventario = new ArrayList<Stock>();
		String sql = "select * from stock where invepers=? and invemate=? and invebode=? and invecent=? and inveesma=? and inveprov=?" +
				"order by depadesc, bodedesc, inveprov, matedesc, centdesc";
		java.sql.PreparedStatement pst = conexion.getConnection().prepareStatement(sql);
		pst.setString(1, this.codigo);
		pst.setString(2, material);
		pst.setString(3, bodega);
		pst.setString(4, centro);
		pst.setString(5, estado);
		pst.setString(6, suministro);
		
		ResultSet rs = conexion.Query(pst);
		while (rs.next()) {
			Stock stock = new Stock();
			stock.setBodega(rs.getString("invebode"));
			stock.setDesc_bodega(rs.getString("bodedesc"));
			stock.setDepartamento(rs.getString("invedepa"));
			stock.setDesc_departamento(rs.getString("depadesc"));
			stock.setCentro(rs.getString("invecent"));
			stock.setDesc_centro(rs.getString("centdesc"));
			stock.setMaterial(rs.getString("invemate"));
			stock.setDesc_material(rs.getString("matedesc"));
			stock.setUnd_material(rs.getString("mateunid"));
			stock.setClasificacion(rs.getInt("matecabl"));
			stock.setCedula(rs.getString("invepers"));
			stock.setSuministro(rs.getString("inveprov"));
			stock.setNum_entrega(rs.getString("inveentr"));
			stock.setEstado(rs.getString("inveesma"));
			stock.setDesc_estado(rs.getString("estadesc"));
			stock.setId(rs.getString("invenuid"));
			stock.setCantidad(rs.getDouble("invecant"));
			stock.setReserva(rs.getDouble("invecare"));
			stock.setSerial("");
			stock.setCasid("");
			inventario.add(stock);
		}
		
		
		return inventario;
	}
	
	public ArrayList<Stock> StockTotalizado() throws SQLException {
		ArrayList<Stock> inventario = new ArrayList<Stock>();
		String sql = "select * from stock_totalizado where invepers=?" +
				"order by depadesc, bodedesc, inveprov, matedesc, centdesc";
		java.sql.PreparedStatement pst = conexion.getConnection().prepareStatement(sql);
		pst.setString(1, this.codigo);
		
		ResultSet rs = conexion.Query(pst);
		while (rs.next()) {
			Stock stock = new Stock();
			stock.setBodega(rs.getString("invebode"));
			stock.setDesc_bodega(rs.getString("bodedesc"));
			stock.setDepartamento(rs.getString("invedepa"));
			stock.setDesc_departamento(rs.getString("depadesc"));
			stock.setCentro(rs.getString("invecent"));
			stock.setDesc_centro(rs.getString("centdesc"));
			stock.setMaterial(rs.getString("invemate"));
			stock.setDesc_material(rs.getString("matedesc"));
			stock.setUnd_material(rs.getString("mateunid"));
			stock.setClasificacion(rs.getInt("matecabl"));
			stock.setCedula(rs.getString("invepers"));
			stock.setSuministro(rs.getString("inveprov"));
			stock.setEstado(rs.getString("inveesma"));
			stock.setDesc_estado(rs.getString("estadesc"));
			stock.setCantidad(rs.getDouble("invecant"));
			stock.setReserva(rs.getDouble("invecare"));
			stock.setSerial("");
			stock.setCasid("");
			inventario.add(stock);
		}
		
		
		return inventario;
	}
	
	public ArrayList<Stock> Stock(int order) throws SQLException {
		ArrayList<Stock> inventario = new ArrayList<Stock>();
		String sql = "select * from stock where stock=?";
			switch(order) {
			case ORDER_DESCRIPCION_MATERIAL:
				sql +="order by matedesc";
				break;
			case ORDER_DESCRIPCION_BODEGA:
				sql +="order by bodedesc";
				break;	
			case ORDER_DESCRIPCION_DEPARTAMENTO:
				sql +="order by depadesc";
				break;
			case ORDER_DESCRIPCION_CENTRO:
				sql +="order by centdesc";
				break;
			case ORDER_DESCRIPCION_SUMINISTRO:
				sql +="order by inveprov";
				break;
			}
		java.sql.PreparedStatement pst = conexion.getConnection().prepareStatement(sql);
		pst.setString(1, this.codigo);
		
		ResultSet rs = conexion.Query(pst);
		while (rs.next()) {
			Stock stock = new Stock();
			stock.setBodega(rs.getString("invebode"));
			stock.setDesc_bodega(rs.getString("bodedesc"));
			stock.setDepartamento(rs.getString("invedepa"));
			stock.setDesc_departamento(rs.getString("depadesc"));
			stock.setCentro(rs.getString("invecent"));
			stock.setDesc_centro(rs.getString("centdesc"));
			stock.setMaterial(rs.getString("invemate"));
			stock.setDesc_material(rs.getString("matedesc"));
			stock.setUnd_material(rs.getString("mateunid"));
			stock.setClasificacion(rs.getInt("matecabl"));
			stock.setCedula(rs.getString("invepers"));
			stock.setSuministro(rs.getString("inveprov"));
			stock.setEstado(rs.getString("inveesma"));
			stock.setDesc_estado(rs.getString("estadesc"));
			stock.setNum_entrega(rs.getString("inveentr"));
			stock.setId(rs.getString("invenuid"));
			stock.setCantidad(rs.getDouble("invecant"));
			stock.setReserva(rs.getDouble("invecare"));
			stock.setSerial("");
			stock.setCasid("");
			inventario.add(stock);
		}
		
		
		return inventario;
	}
	
	public ArrayList<Stock> Seriales() throws SQLException {
		ArrayList<Stock> inventario = new ArrayList<Stock>();
		String sql = "select * from stock_seriales where seripers=? " +
				"order by depadesc, bodedesc, seriprov, matedesc, centdesc";
		java.sql.PreparedStatement pst = conexion.getConnection().prepareStatement(sql);
		pst.setString(1, this.codigo);
		
		ResultSet rs = conexion.Query(pst);
		while (rs.next()) {
			Stock stock = new Stock();
			stock.setBodega(rs.getString("seribode"));
			stock.setDesc_bodega(rs.getString("bodedesc"));
			stock.setDepartamento(rs.getString("seridepa"));
			stock.setDesc_departamento(rs.getString("depadesc"));
			stock.setCentro(rs.getString("sericent"));
			stock.setDesc_centro(rs.getString("centdesc"));
			stock.setMaterial(rs.getString("serimate"));
			stock.setDesc_material(rs.getString("matedesc"));
			stock.setUnd_material(rs.getString("mateunid"));
			stock.setClasificacion(rs.getInt("matecabl"));
			stock.setCedula(rs.getString("seripers"));
			stock.setSuministro(rs.getString("seriprov"));
			stock.setEstado(rs.getString("seriesma"));
			stock.setDesc_estado(rs.getString("estadesc"));
			stock.setNum_entrega(rs.getString("serientr"));
			stock.setId(rs.getString("serinuid"));
			stock.setCantidad(1);
			stock.setSerial(rs.getString("sericodi"));
			stock.setCasid(rs.getString("sericaid"));
			inventario.add(stock);
		}
		
		
		return inventario;
	}
	
	public ArrayList<Stock> FindSeriales(String criterio, int filter) throws SQLException {
		ArrayList<Stock> inventario = new ArrayList<Stock>();
		String sql ="";
		switch (filter){
		case FILTER_BY_TERMINE:
			sql = "select * from stock_seriales where seripers=? and sericodi like ? and serirete=0 " +
					"order by sericodi";
			criterio = "%" +criterio;
			break;
		case FILTER_BY_COMIENCE:
			sql = "select * from stock_seriales where seripers=? and sericodi like ? and serirete=0 " +
					"order by sericodi";
			criterio = criterio + "%";
			break;
		case FILTER_BY_CONTENGA:
			sql = "select * from stock_seriales where seripers=? and sericodi like ? and serirete=0 " +
					"order by sericodi";
			criterio = "%" + criterio + "%";

			break;
		default:
			
		}
		java.sql.PreparedStatement pst = conexion.getConnection().prepareStatement(sql);
		pst.setString(1, this.codigo);
		pst.setString(2, criterio);
		
		ResultSet rs = conexion.Query(pst);
		while (rs.next()) {
			Stock stock = new Stock();
			stock.setBodega(rs.getString("seribode"));
			stock.setDesc_bodega(rs.getString("bodedesc"));
			stock.setDepartamento(rs.getString("seridepa"));
			stock.setDesc_departamento(rs.getString("depadesc"));
			stock.setCentro(rs.getString("sericent"));
			stock.setDesc_centro(rs.getString("centdesc"));
			stock.setMaterial(rs.getString("serimate"));
			stock.setDesc_material(rs.getString("matedesc"));
			stock.setUnd_material(rs.getString("mateunid"));
			stock.setClasificacion(rs.getInt("matecabl"));
			stock.setCedula(rs.getString("seripers"));
			stock.setSuministro(rs.getString("seriprov"));
			stock.setEstado(rs.getString("seriesma"));
			stock.setDesc_estado(rs.getString("estadesc"));
			stock.setNum_entrega(rs.getString("serientr"));
			stock.setId(rs.getString("serinuid"));
			stock.setCantidad(1);
			stock.setSerial(rs.getString("sericodi"));
			stock.setCasid(rs.getString("sericaid"));
			inventario.add(stock);
		}
		
		
		return inventario;
	}
	
	public ArrayList<Stock> Seriales(int order) throws SQLException {
		ArrayList<Stock> inventario = new ArrayList<Stock>();
		String sql = "select * from stock_seriales where seripers=? ";
		switch (order) {
		case ORDER_DESCRIPCION_MATERIAL:
			sql += "order by matedesc";
			break;
		case ORDER_DESCRIPCION_BODEGA:
			sql += "order by bodedesc";
			break;
		case ORDER_DESCRIPCION_DEPARTAMENTO:
			sql += "order by depadesc";
			break;
		case ORDER_DESCRIPCION_CENTRO:
			sql += "order by centdesc";
			break;
		case ORDER_DESCRIPCION_SUMINISTRO:
			sql += "order by inveprov";
			break;
		}
		java.sql.PreparedStatement pst = conexion.getConnection()
				.prepareStatement(sql);
		pst.setString(1, this.codigo);

		ResultSet rs = conexion.Query(pst);
		while (rs.next()) {
			Stock stock = new Stock();
			stock.setBodega(rs.getString("seribode"));
			stock.setDesc_bodega(rs.getString("bodedesc"));
			stock.setDepartamento(rs.getString("seridepa"));
			stock.setDesc_departamento(rs.getString("depadesc"));
			stock.setCentro(rs.getString("sericent"));
			stock.setDesc_centro(rs.getString("centdesc"));
			stock.setMaterial(rs.getString("serimate"));
			stock.setDesc_material(rs.getString("matedesc"));
			stock.setUnd_material(rs.getString("mateunid"));
			stock.setClasificacion(rs.getInt("matecabl"));
			stock.setCedula(rs.getString("seripers"));
			stock.setSuministro(rs.getString("seriprov"));
			stock.setEstado(rs.getString("seriesma"));
			stock.setDesc_estado(rs.getString("estadesc"));
			stock.setNum_entrega(rs.getString("serientr"));
			stock.setId(rs.getString("serinuid"));
			stock.setCantidad(1);
			stock.setSerial(rs.getString("sericodi"));
			stock.setCasid(rs.getString("sericaid"));
			inventario.add(stock);
		}

		return inventario;
	}
	
	public boolean FindSerialById(String id) throws SQLException {
		boolean result = false;
		String sql = "select * from stock_seriales where serinuid = ? ";
		java.sql.PreparedStatement pst = conexion.getConnection()
				.prepareStatement(sql);
		pst.setString(1, id);

		ResultSet rs = conexion.Query(pst);
		if (rs.next()) {
			stock = new Stock();
			stock.setBodega(rs.getString("seribode"));
			stock.setDesc_bodega(rs.getString("bodedesc"));
			stock.setDepartamento(rs.getString("seridepa"));
			stock.setDesc_departamento(rs.getString("depadesc"));
			stock.setCentro(rs.getString("sericent"));
			stock.setDesc_centro(rs.getString("centdesc"));
			stock.setMaterial(rs.getString("serimate"));
			stock.setDesc_material(rs.getString("matedesc"));
			stock.setUnd_material(rs.getString("mateunid"));
			stock.setClasificacion(rs.getInt("matecabl"));
			stock.setCedula(rs.getString("seripers"));
			stock.setSuministro(rs.getString("seriprov"));
			stock.setEstado(rs.getString("seriesma"));
			stock.setDesc_estado(rs.getString("estadesc"));
			stock.setNum_entrega(rs.getString("serientr"));
			stock.setId(rs.getString("serinuid"));
			stock.setCantidad(1);
			stock.setSerial(rs.getString("sericodi"));
			stock.setCasid(rs.getString("sericaid"));
			result = true;
		}
		return result;

	}
	
	public ArrayList<Stock> FindStock(String criterio, int filter) throws SQLException {
		ArrayList<Stock> inventario = new ArrayList<Stock>();
		String sql ="";
		switch (filter) {
		case FILTER_BY_CODIGO:
			sql = "select * from stock where invepers=? and invemate like ? and matecabl != 2 " +
					"order by matedesc,invenuid";
			break;
		case FILTER_BY_DESCRIPCION:
			sql = "select * from stock where invepers=? and matedesc like ? and matecabl != 2 " +
					"order by matedesc,invenuid";
			break;
		default:
			sql = "select * from stock where invepers=? and matedesc like ? and matecabl != 2 " +
					"order by matedesc,invenuid";
		}
		
		java.sql.PreparedStatement pst = conexion.getConnection().prepareStatement(sql);
		pst.setString(1, this.codigo);
		pst.setString(2, "%" + criterio + "%");
		
		ResultSet rs = conexion.Query(pst);
		while (rs.next()) {
			Stock stock = new Stock();
			stock.setBodega(rs.getString("invebode"));
			stock.setDesc_bodega(rs.getString("bodedesc"));
			stock.setDepartamento(rs.getString("invedepa"));
			stock.setDesc_departamento(rs.getString("depadesc"));
			stock.setCentro(rs.getString("invecent"));
			stock.setDesc_centro(rs.getString("centdesc"));
			stock.setMaterial(rs.getString("invemate"));
			stock.setDesc_material(rs.getString("matedesc"));
			stock.setUnd_material(rs.getString("mateunid"));
			stock.setClasificacion(rs.getInt("matecabl"));
			stock.setCedula(rs.getString("invepers"));
			stock.setSuministro(rs.getString("inveprov"));
			stock.setNum_entrega(rs.getString("inveentr"));
			stock.setEstado(rs.getString("inveesma"));
			stock.setDesc_estado(rs.getString("estadesc"));
			stock.setId(rs.getString("invenuid"));
			stock.setCantidad(rs.getDouble("invecant"));
			stock.setReserva(rs.getDouble("invecare"));
			stock.setSerial("");
			stock.setCasid("");
			inventario.add(stock);
		}
		
		
		return inventario;
	}
	
	public boolean FindStockById(String id) throws SQLException {
		boolean result = false;
		String sql ="select * from stock where invenuid=?";
		java.sql.PreparedStatement pst = conexion.getConnection().prepareStatement(sql);
		pst.setString(1, id);
		ResultSet rs = conexion.Query(pst);
		if (rs.next()) {
			stock = new Stock();
			stock.setBodega(rs.getString("invebode"));
			stock.setDesc_bodega(rs.getString("bodedesc"));
			stock.setDepartamento(rs.getString("invedepa"));
			stock.setDesc_departamento(rs.getString("depadesc"));
			stock.setCentro(rs.getString("invecent"));
			stock.setDesc_centro(rs.getString("centdesc"));
			stock.setMaterial(rs.getString("invemate"));
			stock.setDesc_material(rs.getString("matedesc"));
			stock.setUnd_material(rs.getString("mateunid"));
			stock.setClasificacion(rs.getInt("matecabl"));
			stock.setCedula(rs.getString("invepers"));
			stock.setSuministro(rs.getString("inveprov"));
			stock.setNum_entrega(rs.getString("inveentr"));
			stock.setEstado(rs.getString("inveesma"));
			stock.setDesc_estado(rs.getString("estadesc"));
			stock.setId(rs.getString("invenuid"));
			stock.setCantidad(rs.getDouble("invecant"));
			stock.setSerial("");
			stock.setCasid("");
			result = true;
		}
		
		
		return result;
	}
	
	
}
